﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Security.Cryptography;
using Analysis.Entities;
using Dapper;
using System.Data;
using System.Data.SqlClient;

namespace Analysis.DAL.Repositories
{
    public class TargetSCURepository : BaseDataRepository
    {
        public static DataTable FindTargetSCU(int monthId, int YearId)
        {
            return DataAccess.GetDataTable("exec spTargetSCUList @MonthId = @MonthId, @YearId = @YearId",
                new string[] { "@MonthId", "@YearId" }, new object[] { monthId, YearId });
        }

        public static DataTable YearCustomCombo()
        {
            string strSQL = string.Format(@"select distinct YearId from Sale (nolock)
                            union select {0} union select {1} union select {2} union select {3}
                            order by 1", 
                                       DateTime.Today.Year.ToString(), 
                                       DateTime.Today.AddMonths(1).Year.ToString(), 
                                       DateTime.Today.AddMonths(2).Year.ToString(),
                                       DateTime.Today.AddMonths(3).Year.ToString());
            return DataAccess.GetDataTable(strSQL);
        }

        public static DataTable MonthCustomCombo(int yearId)
        {
            string strSQL = string.Format(@"select Months.MonthId, Months.MonthName 
                            from (select distinct MonthId, YearId
                                    from Sale (nolock) 
                                  union select {1}, {5}
                                  union select {2}, {6} 
                                  union select {3}, {7} 
                                  union select {4}, {8}) as vw left join Months (nolock) on vw.MonthId = Months.MonthId
                            where vw.YearId = {0}
                            order by 1", yearId.ToString(),
                                       DateTime.Today.Month.ToString(),
                                       DateTime.Today.AddMonths(1).Month.ToString(),
                                       DateTime.Today.AddMonths(2).Month.ToString(),
                                       DateTime.Today.AddMonths(3).Month.ToString(),
                                       DateTime.Today.Year.ToString(),
                                       DateTime.Today.AddMonths(1).Year.ToString(),
                                       DateTime.Today.AddMonths(2).Year.ToString(),
                                       DateTime.Today.AddMonths(3).Year.ToString());
            return DataAccess.GetDataTable(strSQL);
        }
        
        public static void EditTargetSCU(int id, List<KeyValuePair<string, object>> data)
        {
            string strSQL;
            List<string> parameters = new List<string>();
            List<object> values = new List<object>();

            if (id == -1)
            {
                strSQL = "insert into TargetSCU ";
                strSQL += GenerateInsertDynamic(data, parameters, values);
            }
            else
            {
                strSQL = "update TargetSCU set ";
                strSQL += GenerateUpdateDynamic(data, parameters, values);
                strSQL += " where TargetSCUId = " + id.ToString();
            }
            DataAccess.ExecuteSQL(strSQL, parameters.ToArray(), values.ToArray()); 
        }

        public static void DeleteTargetSCU(int targetSCUId)
        {
            DataAccess.ExecuteSQL("delete from TargetSCU where TargetSCUId = @TargetSCUId", new string[] { "@TargetSCUId" }, new object[] { targetSCUId });
        }
    }
}
